﻿using System;
using System.Data;
using System.Data.SqlServerCe;
using System.Linq;
using System.Collections.Generic;
using System.Text;
using System.Xml;
using System.Xml.Linq;
using Common;

namespace ImpExpData
{
    abstract public class ImportData
    {
        public string Name { get; set; }
        public string FileName { get; set; }
        public string TableName{ get; set;}
        protected DataRowCollection cols;

        protected abstract void InsertData(SqlCeResultSet rs, XElement element);
        protected abstract void UpdateData(SqlCeResultSet rs, XElement element);
        protected abstract void DeleteData(SqlCeResultSet rs, XElement element);


        public void LoadData()
        {
            try
            {
                if (WorkDataBase.GetInstance().Connection.State != ConnectionState.Open)
                {
                    WorkDataBase.GetInstance().Connection.Open();
                }
            }
            catch(Exception)
            {
                throw new Exception("Неможливо підєднатись до БД");
            }

            try
            {
                using (SqlCeCommand command = new SqlCeCommand(TableName, WorkDataBase.GetInstance().Connection))
                {
                    XDocument xml = XDocument.Load(FileName);

                    IEnumerable<XElement> elements = xml.Root.Elements().OrderBy(e => e.Attributes().ElementAt(0));
                    command.CommandType = CommandType.TableDirect;
                    using (SqlCeResultSet rs = command.ExecuteResultSet(ResultSetOptions.Updatable))
                    {
                        cols = rs.GetSchemaTable().Rows;
                        int indDb = 0, indImp = 0;
                        bool xmlGo = true, rsGo = true;
                        IEnumerable<XElement> xmlData = xml.Root.Elements().OrderBy(e => e.Attribute(cols[0]["ColumnName"].ToString()).Value);
                        XElement element;
                        int row = 0, count = xmlData.Count();

                        while (true)
                        {
                            if (xmlGo)
                                row++;
                            element = row < count ? xmlData.ElementAt(row) : null;
                            indImp = element != null
                                         ? int.Parse(element.Attribute(cols[0]["ColumnName"].ToString()).Value)
                                         : int.MaxValue;

                            indDb = rsGo && rs.Read() ? rs.GetInt32(0) : int.MaxValue;

                            if (indDb == int.MaxValue && indImp == int.MaxValue)
                                break;

                            if (indDb == indImp)
                            {
                                UpdateData(rs, element);
                                rsGo = true;
                                xmlGo = true;
                                continue;
                            }

                            if (indDb > indImp)
                            {
                                InsertData(rs, element);
                                rsGo = false;
                                xmlGo = true;
                                continue;
                            }

                            if (indDb < indImp)
                            {
                                DeleteData(rs, element);
                                rsGo = true;
                                xmlGo = false;
                                continue;
                            }
                        }
                    }
                }
            }
            finally
            {
                
                WorkDataBase.GetInstance().Connection.Close();
            }
        }
    }
}

